import pandas as pd
from models import db, Bill
from app import app
from datetime import datetime
from sqlalchemy import or_


def handle_nullable_field(model_field, value):
    if pd.isna(value) or value == '':
        return or_(model_field.is_(None), model_field == '')
    else:
        return model_field == value

def import_bill_data(file_path):
    with app.app_context():
        try:
            # 读取Excel文件
            df = pd.read_excel(file_path)
            
            # 筛选需要的列并处理数据
            df = df[['日期', '收支类型', '金额', '类别', '二级分类', '账户', '账本', '备注']]
            df = df.dropna(subset=['日期', '收支类型', '金额', '类别'])
            
            num=0
            # 导入数据到数据库
            for _, row in df.iterrows():
                bill_date=row['日期'].to_pydatetime() if isinstance(row['日期'], pd.Timestamp) else datetime.strptime(row['日期'], '%Y-%m-%d %H:%M')
                exists = Bill.query.filter(
                    Bill.date == bill_date,
                    Bill.type == row['收支类型'],
                    Bill.amount == float(row['金额']),
                    Bill.category == row['类别'],
                    handle_nullable_field(Bill.subcategory, row['二级分类']),
                    handle_nullable_field(Bill.account, row['账户']),
                    handle_nullable_field(Bill.remark, row['备注'])
                ).first()
                print(exists)
                if not exists:
                    num+=1
                    bill = Bill(
                        date=bill_date,
                        type=row['收支类型'],
                        amount=float(row['金额']),
                        category=row['类别'],
                        subcategory=row['二级分类'],
                        account=row['账户'],
                        ledger=row['账本'],
                        remark=row['备注']
                    )
                    db.session.add(bill)
            if num>0:
                db.session.commit()
                print(f"成功导入 {num} 条账单记录")
            else:
                print("没有新的账单记录需要导入")
            return True
            
        except Exception as e:
            db.session.rollback()
            print(f"导入数据出错: {e}")
            return False

if __name__ == "__main__":
    import_bill_data("账单.xlsx")